﻿using Excel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using UnityEngine;

namespace SG
{
	public class ExcelTable
	{
		private List<Sheet> mSheetList;
		private Dictionary<string, Sheet> mSheetDict;

		public ExcelTable(string path, bool ignoreEmptyRow = false)
		{
			mSheetList = new List<Sheet>();
			mSheetDict = new Dictionary<string, Sheet>();

			FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
			IExcelDataReader reader = null;
			if (path.EndsWith(".xls")) {
				reader = ExcelReaderFactory.CreateBinaryReader(stream);
			} else if (path.EndsWith(".xlsx")) {
				reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
			} else {
				throw new ArgumentException("The file is not Excel-file");
			}
			do {
				Sheet sheet = new Sheet(reader, ignoreEmptyRow);
				mSheetList.Add(sheet);
				mSheetDict.Add(sheet.mName, sheet);
			} while (reader.NextResult());
			reader.Close();
			stream.Close();
		}

		public Sheet this[int index]
		{
			get
			{
				if (index >= 0 && index < mSheetList.Count) {
					return mSheetList[index];
				}
				return null;
			}
		}

		public Sheet this[string name]
		{
			get
			{
				if (mSheetDict.ContainsKey(name)) {
					return mSheetDict[name];
				}
				return null;
			}
		}

		public class Sheet
		{
			public int colCount;
			public int rowCount;
			public string mName;
			private List<List<Cell>> mRows;

			public Sheet(IExcelDataReader reader, bool ignoreEmptyRow = false)
			{
				mName = reader.Name;
				mRows = new List<List<Cell>>();
				while (reader.Read()) {
					colCount = reader.FieldCount;
					List<Cell> cellList = new List<Cell>();
					int emptyCount = 0;
					for (int i = 0; i < colCount; i++) {
						string text = "";
						if (reader.IsDBNull(i)) {
							emptyCount++;
						} else {
							text = reader.GetString(i);
						}
						Cell cell = new Cell(text);
						cell.sheet = this;
						cell.rowIndex = rowCount;
						cell.index = i;
						cellList.Add(cell);
					}
					if (!ignoreEmptyRow || emptyCount != colCount) {
						mRows.Add(cellList);
						++rowCount;
					}
				}
			}

			public Cell GetCell(int row, int col)
			{
				return mRows[row][col];
			}
			public List<Cell> GetRow(int row)
			{
				return mRows[row];
			}
		}

		public class Cell
		{
			private string mValue;
			public Sheet sheet;
			public int rowIndex;
			public int index;

			public Cell(string value)
			{
				mValue = value;
			}

			public int ToInt()
			{
				return Convert.ToInt32(mValue);
			}
			public float ToFloat()
			{
				return Convert.ToSingle(mValue);
			}
			public bool ToBoolean()
			{
				return Convert.ToBoolean(mValue);
			}
			public override string ToString()
			{
				return mValue;
			}

			public bool IsEmpty()
			{
				return string.IsNullOrEmpty(mValue);
			}
		}
	}
}